﻿'========================================================================
'功能:員工檔資料庫存取物件
'作者:andy
'日期: 2011/2/9
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace OAMS.DAO
    Public Class UsersDAO
        Inherits BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub




        ''' <summary>
        ''' 設定語言別到mySqlDB中
        ''' </summary>
        ''' <param name="UID">使用者代號</param>
        ''' <param name="UICulture">語言別</param>
        ''' <param name="dbName">資料庫代號</param>
        ''' <remarks> 設定語言別到mySqlDB中</remarks>

        Public Sub SetLanguage(ByVal UID As String, ByVal UICulture As String)

            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@UICulture", SqlDbType.NVarChar)
            sqlParams(1).Value = UICulture
            Dim sb As New StringBuilder()
            sb.AppendLine("Delete from SC_UICulture where UID=@UID")
            sb.AppendLine("insert into SC_UICulture(UID,UICulture)values(@UID,@UICulture)")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

            

        End Sub

        ''' <summary>
        ''' 取得語言別到mySqlDB中
        ''' </summary>
        ''' <param name="UID">使用者代號</param>
        ''' <param name="dbName">資料庫代號</param>
        '''<returns>取得語言別到mySqlDB中</returns>
        ''' <remarks> 取得語言別到mySqlDB中</remarks>
        Public Function GetLanguage(ByVal UID As String) As String

            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID

            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from  SC_UICulture where UID=@UID")

            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            If dt.Rows.Count = 0 Then
                Return "en-us"
            Else
                Return dt.Rows(0)("UICulture").ToString


            End If




        End Function


        ''' <summary>
        ''' 檢查某一個工號是否在資料庫中
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>檢查某一個工號是否在資料庫中</returns>
        ''' <remarks>檢查某一個工號是否在資料庫中</remarks>
        Public Function UserIsExist(ByVal UID As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID


            Dim sb As New StringBuilder()

            sb.AppendLine(" select * from auoref.dbo.emp_data_all where emp_no =@UID")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function
        ''' <summary>
        ''' 查詢員工基本資料傳入工號或姓名都可以空白代表全部
        ''' </summary>
        ''' <param name="UIDOrUserName"></param>
        ''' <returns>查詢員工基本資料傳入工號或姓名都可以空白代表全部</returns>
        ''' <remarks>查詢員工基本資料傳入工號或姓名都可以空白代表全部</remarks>
        Public Function GetUsers(ByVal UIDOrUserName As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UIDOrUserName


            Dim sb As New StringBuilder()

            sb.AppendLine(" select emp_no as UserID ,emp_name as UserName ,e_mail as EMail,org_id as Dept_ID , site_name as Factory_ID ,LOGONID as login_id,come_date as TakeOfficeDate,birthday as Birth_day,ext_no as Ext_no")
            sb.AppendLine(" from auoref.dbo.emp_data_all where emp_no like '%" + UIDOrUserName + "%' or emp_name like '%" + UIDOrUserName + "%'")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        ''' <summary>
        ''' 取得使用者姓名
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得使用者姓名</returns>
        ''' <remarks>取得使用者姓名</remarks>
        Public Function GetUserName(ByVal UID As String) As String
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID


            Dim sb As New StringBuilder()

            sb.AppendLine(" select emp_name as UserName from AUOREF.dbo.emp_data_all  where emp_no =@UID ")
            Try
                Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString()
            Catch ex As Exception
                Return ""
            End Try

        End Function

        Public Function GetURLRights(ByVal URL As String, ByVal QueryString As String, ByVal UserID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("  select distinct A.ProgramID,B.SeqNo,B.DisplayName,A.Name,A.URL ,C.UseRight,A.isMenu ")
            sb.AppendLine(" from Menu A")
            sb.AppendLine(" left join MenuRights B on A.ProgramID=B.ProgramID")
            sb.AppendLine(" left join GroupRights C on C.ProgramID=B.ProgramID and  C.SeqNo=B.SeqNo")
            sb.AppendLine(" left join GroupMember D on C.GroupID=D.GroupID ")
            sb.AppendLine(" where ((A.URL='" & URL & "' and A.QueryString=0) or (A.QueryString=1 and A.URL='" & QueryString & "')) ")
            sb.AppendLine("and ( (A.NeedControl=0)   or   ( D.UID='" & UserID & "'  and C.UseRight=1) ) ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
            
        End Function

        Public Function GetUsersLogin(ByVal LoginID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@Loginid", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = LoginID


            Dim sb As New StringBuilder()

            sb.AppendLine(" select emp_no as UserID ,emp_name as UserName ,e_mail as EMail,org_id as Dept_ID , site_name as Factory_ID ,LOGONID as login_id,come_date as TakeOfficeDate,birthday as Birth_day,ext_no as Ext_no")
            sb.AppendLine(" from auoref.dbo.emp_data_all where LOGONID =@LoginID")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


    End Class


End Namespace


